﻿Imports System.Data.SqlClient
Public Class frmRPagobusDetallado
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal

    Private Sub frmRPagobusDetallado_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Bitacora.RegistraActividad("Cerró al Reporte de Pagobus")
        Usuario.Ubicacion("Módulo de Pagobus")
    End Sub
    Private Sub frmRPagobusDetallado_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        datFecha1.Value = Now()


        lstA.Items.Clear()
        lstR.Items.Clear()
        cargaAutobuses()
        CargaRutas()
        RadioButton1.Checked = True

        Bitacora.RegistraActividad("Ingresó al Reporte de Detallado de Pagobus")
        Usuario.Ubicacion("Reporte de Detallado de Pagobus")
    End Sub
    Sub CargaRutas()
        lstRuta.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection

            strSql = "SELECT Nombre FROM RUTAS"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                Me.lstRuta.Items.Add(rdBuscar("Nombre").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try
    End Sub
    Sub cargaAutobuses()
        lstAutobus.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection

            strSql = "select NoEconomico from Autobuses where Estatus=1 ORDER BY NoEconomico"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader
            Dim primero As Boolean
            primero = True

            Do While rdBuscar.Read()
                Me.lstAutobus.Items.Add(rdBuscar("NoEconomico").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try

    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim aux As String = ""
        aux = lstRuta.Text.ToString.Trim
        If aux <> "" Then
            lstR.Items.Add(lstRuta.SelectedItem)
            lstRuta.Items.Remove(lstRuta.SelectedItem)
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim aux As String = ""
        aux = lstR.Text.ToString.Trim
        If aux <> "" Then
            lstRuta.Items.Add(lstR.SelectedItem)
            lstR.Items.Remove(lstR.SelectedItem)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim aux As String = ""
        aux = lstAutobus.Text.ToString.Trim
        If aux <> "" Then
            lstA.Items.Add(lstAutobus.SelectedItem)
            lstAutobus.Items.Remove(lstAutobus.SelectedItem)
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim aux As String = ""
        aux = lstA.Text.ToString.Trim
        If aux <> "" Then
            lstAutobus.Items.Add(lstA.SelectedItem)
            lstA.Items.Remove(lstA.SelectedItem)
        End If
    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = True
        lstRuta.Enabled = True
    End Sub

    Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
        lstA.Enabled = True
        lstAutobus.Enabled = True
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        'Registra actividad


        If RadioButton1.Checked = True Then
            todos()
            Bitacora.RegistraActividad("Imprimió el reporte detallado de pagobus del dia " & datFecha1.Text)
        ElseIf RadioButton2.Checked = True Then
            If lstR.Items.Count = 0 Then
                MsgBox("Debes indicar al menos una ruta", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            Ruta()
            Bitacora.RegistraActividad("Imprimió el reporte detallado de pagobus del dia" & datFecha1.Text & ", Filtrado por Ruta")
        Else
            If lstA.Items.Count = 0 Then
                MsgBox("Debes indicar al menos un autobus", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            Autobus()
            Bitacora.RegistraActividad("Imprimió el reporte detallado de pagobus del dia" & datFecha1.Text & ", Filtrado por Autobus")
        End If
    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("MM/dd/yyyy")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = " SELECT * " & _
                 " FROM Pagobus " & _
                 " WHERE convert(varchar,fecha,101)='" & strFecha1 & "'" & _
                 " ORDER BY NombreRuta,NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        Dim totalG As Double = 0
        Dim TotalP As Double = 0
        Dim totalV As Double = 0
        Dim totalIG As Double = 0
        Dim TotalIP As Double = 0
        Dim totalIV As Double = 0
        Dim totalTer As Double = 0

        Dim totalGG As Double = 0
        Dim TotalPG As Double = 0
        Dim totalVG As Double = 0
        Dim totalIGG As Double = 0
        Dim TotalIPG As Double = 0
        Dim totalIVG As Double = 0
        Dim totalTerG As Double = 0


        wb.SHEETS(1).columns(2).columnwidth = 15
        wb.SHEETS(1).columns(3).columnwidth = 14
        wb.SHEETS(1).columns(4).columnwidth = 14
        wb.SHEETS(1).columns(5).columnwidth = 14
        wb.SHEETS(1).columns(6).columnwidth = 14
        wb.SHEETS(1).columns(7).columnwidth = 14
        wb.SHEETS(1).columns(8).columnwidth = 14
        wb.SHEETS(1).columns(9).columnwidth = 14
        wb.SHEETS(1).columns(10).columnwidth = 14

        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(7).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(9).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(10).NumberFormat = "#,##0.00"

        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("NombreRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 2).value = "Totales"
                    wb.SHEETS(1).cells(rng, 3).value = totalG
                    wb.SHEETS(1).cells(rng, 4).value = TotalP
                    wb.SHEETS(1).cells(rng, 5).value = totalTer
                    wb.SHEETS(1).cells(rng, 6).value = totalV
                    wb.SHEETS(1).cells(rng, 7).value = totalIG
                    wb.SHEETS(1).cells(rng, 8).value = TotalIP
                    wb.SHEETS(1).cells(rng, 9).value = 0
                    wb.SHEETS(1).cells(rng, 10).value = totalIV

                    For i = 2 To 10
                        wb.SHEETS(1).cells(rng, i).font.italic = True
                    Next
                    totalG = 0
                    TotalP = 0
                    totalV = 0
                    totalIG = 0
                    TotalIP = 0
                    totalIV = 0
                    totalTer = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreRuta").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).font.size = 12
                wb.SHEETS(1).cells(rng, 2).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 2).value = "Autobus"
                wb.SHEETS(1).cells(rng, 3).value = "General"
                wb.SHEETS(1).cells(rng, 4).value = "Preferencial"
                wb.SHEETS(1).cells(rng, 5).value = "Tercera Edad"
                wb.SHEETS(1).cells(rng, 6).value = "Validaciones"
                wb.SHEETS(1).cells(rng, 7).value = "Importe General"
                wb.SHEETS(1).cells(rng, 8).value = "Importe Preferencial"
                wb.SHEETS(1).cells(rng, 9).value = "Importe 3ra Edad"
                wb.SHEETS(1).cells(rng, 10).value = "Importe Total"

                For i = 1 To 10
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                    wb.SHEETS(1).CELLS(rng, i).HorizontalAlignment = -4108
                    wb.SHEETS(1).CELLS(rng, i).VerticalAlignment = -4108
                    wb.SHEETS(1).CELLS(rng, i).WrapText = True
                    wb.SHEETS(1).CELLS(rng, i).Orientation = 0
                    wb.SHEETS(1).CELLS(rng, i).AddIndent = False
                    wb.SHEETS(1).CELLS(rng, i).IndentLevel = 0
                    wb.SHEETS(1).CELLS(rng, i).ShrinkToFit = False
                    wb.SHEETS(1).CELLS(rng, i).MergeCells = False
                    wb.SHEETS(1).CELLS(rng, i).ReadingOrder = -5002
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("NombreRuta").ToString.Trim
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NoEconomico").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("General")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Preferencial")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("TerceraEdad")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("validaciones")
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("IngresoGeneral")
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("IngresoPreferencial")
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("ImporteTerceraEdad")
            wb.SHEETS(1).cells(rng, 10).value = rdBuscar("ingresovalidaciones")

            totalG = totalG + wb.sheets(1).cells(rng, 3).value
            TotalP = TotalP + wb.sheets(1).cells(rng, 4).value
            totalTer = totalTer + wb.sheets(1).cells(rng, 5).value
            totalV = totalV + wb.sheets(1).cells(rng, 6).value
            totalIG = totalIG + wb.sheets(1).cells(rng, 7).value
            TotalIP = TotalIP + wb.sheets(1).cells(rng, 8).value
            totalIV = totalIV + wb.sheets(1).cells(rng, 10).value


            totalGG = totalGG + wb.sheets(1).cells(rng, 3).value
            TotalPG = TotalPG + wb.sheets(1).cells(rng, 4).value
            totalTerG = totalTerG + wb.sheets(1).cells(rng, 5).value
            totalVG = totalVG + wb.sheets(1).cells(rng, 6).value
            totalIGG = totalIGG + wb.sheets(1).cells(rng, 7).value
            TotalIPG = TotalIPG + wb.sheets(1).cells(rng, 8).value
            totalIVG = totalIVG + wb.sheets(1).cells(rng, 10).value
            rng = rng + 1
        Loop
        wb.SHEETS(1).cells(rng, 2).value = "Totales"
        wb.SHEETS(1).cells(rng, 3).value = totalG
        wb.SHEETS(1).cells(rng, 4).value = TotalP
        wb.SHEETS(1).cells(rng, 5).value = totalTer
        wb.SHEETS(1).cells(rng, 6).value = totalV
        wb.SHEETS(1).cells(rng, 7).value = totalIG
        wb.SHEETS(1).cells(rng, 8).value = TotalIP
        wb.SHEETS(1).cells(rng, 9).value = 0
        wb.SHEETS(1).cells(rng, 10).value = totalIV

        For i = 2 To 10
            wb.SHEETS(1).cells(rng, i).font.italic = True
        Next

        rng = rng + 2
        wb.SHEETS(1).cells(rng, 2).value = "Total General"
        wb.SHEETS(1).cells(rng, 3).value = totalGG
        wb.SHEETS(1).cells(rng, 4).value = TotalPG
        wb.SHEETS(1).cells(rng, 5).value = totalTerG
        wb.SHEETS(1).cells(rng, 6).value = totalVG
        wb.SHEETS(1).cells(rng, 7).value = totalIGG
        wb.SHEETS(1).cells(rng, 8).value = TotalIPG
        wb.SHEETS(1).cells(rng, 9).value = 0
        wb.SHEETS(1).cells(rng, 10).value = totalIVG

        For i = 2 To 10
            wb.SHEETS(1).cells(rng, i).font.italic = True
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next

        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Pagobus del " & Me.datFecha1.Value.ToString("d")
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub
    Sub Autobus()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("MM/dd/yyyy")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        Dim t As Integer = lstA.Items.Count
        Dim strBUSES As String = "'"
        For i = 0 To t - 2
            strBUSES = strBUSES & Me.lstA.Items(i).ToString.Trim & "','"
        Next
        strBUSES = strBUSES & Me.lstA.Items(t - 1).ToString.Trim & "'"

        strSql = " SELECT IdLiquidacion,NombreRuta,NoEconomico,NombreOperador,TotalBoletos,Liquidacion,Vueltas,TipoLiquidacion " & _
                 " FROM Liquidacion " & _
                 " WHERE convert(varchar,fecha,101)='" & strFecha1 & "' AND NoEconomico IN (" & strBUSES & ") " & _
                 " ORDER BY NombreRuta,NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        Dim totalUB As Double = 0
        Dim TotalL As Double = 0
        Dim totalV As Double = 0

        Dim totalUBG As Double = 0
        Dim TotalLG As Double = 0
        Dim totalVG As Double = 0

        wb.SHEETS(1).columns(2).columnwidth = 10
        wb.SHEETS(1).columns(3).columnwidth = 15
        wb.SHEETS(1).columns(4).columnwidth = 30
        wb.SHEETS(1).columns(5).columnwidth = 10
        wb.SHEETS(1).columns(6).columnwidth = 10
        wb.SHEETS(1).columns(7).columnwidth = 10
        wb.SHEETS(1).columns(8).columnwidth = 12

        For i = 5 To 8
            wb.SHEETS(1).columns(i).NumberFormat = "#,##0.0"
        Next
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"

        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("NombreRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    For j = 2 To 8
                        wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

                    Next
                    wb.SHEETS(1).cells(rng, 4).value = "Totales"
                    wb.SHEETS(1).cells(rng, 5).value = totalUB
                    wb.SHEETS(1).cells(rng, 6).value = TotalL
                    wb.SHEETS(1).cells(rng, 7).value = totalV
                    For i = 4 To 7
                        wb.SHEETS(1).cells(rng, i).font.italic = True
                    Next
                    totalUB = 0
                    TotalL = 0
                    totalV = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreRuta").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).font.size = 12
                wb.SHEETS(1).cells(rng, 2).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 2).value = "Folio"
                wb.SHEETS(1).cells(rng, 3).value = "Autobus"
                wb.SHEETS(1).cells(rng, 4).value = "Nombre Operador"
                wb.SHEETS(1).cells(rng, 5).value = "Boletos / Liquidación"
                wb.SHEETS(1).cells(rng, 6).value = "Importe"
                wb.SHEETS(1).cells(rng, 7).value = "Vueltas"
                wb.SHEETS(1).cells(rng, 8).value = "Tipo Liquidación"

                For i = 1 To 8
                    wb.SHEETS(1).cells(rng, i).FONT.name = "Segoe UI Light"
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                    wb.SHEETS(1).CELLS(rng, i).HorizontalAlignment = -4108
                    wb.SHEETS(1).CELLS(rng, i).VerticalAlignment = -4108
                    wb.SHEETS(1).CELLS(rng, i).WrapText = True
                    wb.SHEETS(1).CELLS(rng, i).Orientation = 0
                    wb.SHEETS(1).CELLS(rng, i).AddIndent = False
                    wb.SHEETS(1).CELLS(rng, i).IndentLevel = 0
                    wb.SHEETS(1).CELLS(rng, i).ShrinkToFit = False
                    wb.SHEETS(1).CELLS(rng, i).MergeCells = False
                    wb.SHEETS(1).CELLS(rng, i).ReadingOrder = -5002
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("NombreRuta").ToString.Trim
            For j = 2 To 8
                wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

            Next
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("IdLiquidacion")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NoEconomico").ToString.Trim
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("NombreOperador").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("TotalBoletos")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("Liquidacion")
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Vueltas")
            If rdBuscar("TipoLiquidacion") = 0 Then
                wb.SHEETS(1).cells(rng, 8).value = "Por Boletos"
            Else
                wb.SHEETS(1).cells(rng, 8).value = "Por Cuota"
            End If

            totalUB = totalUB + wb.SHEETS(1).cells(rng, 5).value
            TotalL = TotalL + wb.SHEETS(1).cells(rng, 6).value
            totalV = totalV + wb.SHEETS(1).cells(rng, 7).value

            totalUBG = totalUBG + wb.SHEETS(1).cells(rng, 5).value
            TotalLG = TotalLG + wb.SHEETS(1).cells(rng, 6).value
            totalVG = totalVG + wb.SHEETS(1).cells(rng, 7).value
            rng = rng + 1
        Loop
        For j = 2 To 8
            wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

        Next
        wb.SHEETS(1).cells(rng, 4).value = "Totales"
        wb.SHEETS(1).cells(rng, 5).value = totalUB
        wb.SHEETS(1).cells(rng, 6).value = TotalL
        wb.SHEETS(1).cells(rng, 7).value = totalV
        For i = 4 To 7
            wb.SHEETS(1).cells(rng, i).font.italic = True
        Next

        rng = rng + 2
        For j = 2 To 8
            wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

        Next
        wb.SHEETS(1).cells(rng, 2).value = "Total Autobuses: " & BuscarIntDatoCitra("SELECT COUNT(*) FROM Liquidacion WHERE convert(varchar,fecha,101)='" & strFecha1 & "'")
        wb.SHEETS(1).cells(rng, 2).font.bold = True
        wb.SHEETS(1).cells(rng, 4).value = "Total General"
        wb.SHEETS(1).cells(rng, 5).value = totalUBG
        wb.SHEETS(1).cells(rng, 6).value = TotalLG
        wb.SHEETS(1).cells(rng, 7).value = totalVG
        For i = 4 To 7
            wb.SHEETS(1).cells(rng, i).font.italic = True
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next

        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(1, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Liquidacion del " & Me.datFecha1.Value.ToString("d") & " (Filtrado por Autobus)"
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        wb.SHEETS(1).cells(3, 3).FONT.name = "Segoe UI Light"

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub
    Sub Ruta()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha1.Value.ToString("MM/dd/yyyy")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        Dim clasifi As Integer
        Dim t As Integer = lstR.Items.Count
        Dim strRutas As String = ""

        For i = 0 To t - 2
            clasifi = BuscarIntDatoCitra("RUTAS", "IdRuta", "Nombre='" & Me.lstR.Items(i).ToString.Trim & "'")
            strRutas = strRutas & clasifi & ","
        Next
        clasifi = BuscarIntDatoCitra("RUTAS", "IdRuta", "Nombre='" & Me.lstR.Items(t - 1).ToString.Trim & "'")
        strRutas = strRutas & clasifi

        strSql = " SELECT IdLiquidacion,NombreRuta,NoEconomico,NombreOperador,TotalBoletos,Liquidacion,Vueltas,TipoLiquidacion " & _
                 " FROM Liquidacion " & _
                 " WHERE convert(varchar,fecha,101)='" & strFecha1 & "' AND IdRuta IN (" & strRutas & ") " & _
                 " ORDER BY NombreRuta,NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        Dim totalUB As Double = 0
        Dim TotalL As Double = 0
        Dim totalV As Double = 0

        Dim totalUBG As Double = 0
        Dim TotalLG As Double = 0
        Dim totalVG As Double = 0

        wb.SHEETS(1).columns(2).columnwidth = 10
        wb.SHEETS(1).columns(3).columnwidth = 15
        wb.SHEETS(1).columns(4).columnwidth = 30
        wb.SHEETS(1).columns(5).columnwidth = 10
        wb.SHEETS(1).columns(6).columnwidth = 10
        wb.SHEETS(1).columns(7).columnwidth = 10
        wb.SHEETS(1).columns(8).columnwidth = 12

        For i = 5 To 8
            wb.SHEETS(1).columns(i).NumberFormat = "#,##0.0"
        Next
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"

        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("NombreRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    For j = 2 To 8
                        wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

                    Next
                    wb.SHEETS(1).cells(rng, 4).value = "Totales"
                    wb.SHEETS(1).cells(rng, 5).value = totalUB
                    wb.SHEETS(1).cells(rng, 6).value = TotalL
                    wb.SHEETS(1).cells(rng, 7).value = totalV
                    For i = 4 To 7
                        wb.SHEETS(1).cells(rng, i).font.italic = True
                    Next
                    totalUB = 0
                    TotalL = 0
                    totalV = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreRuta").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).font.size = 12
                wb.SHEETS(1).cells(rng, 2).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 2).value = "Folio"
                wb.SHEETS(1).cells(rng, 3).value = "Autobus"
                wb.SHEETS(1).cells(rng, 4).value = "Nombre Operador"
                wb.SHEETS(1).cells(rng, 5).value = "Boletos / Liquidación"
                wb.SHEETS(1).cells(rng, 6).value = "Importe"
                wb.SHEETS(1).cells(rng, 7).value = "Vueltas"
                wb.SHEETS(1).cells(rng, 8).value = "Tipo Liquidación"

                For i = 1 To 8
                    wb.SHEETS(1).cells(rng, i).FONT.name = "Segoe UI Light"
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                    wb.SHEETS(1).CELLS(rng, i).HorizontalAlignment = -4108
                    wb.SHEETS(1).CELLS(rng, i).VerticalAlignment = -4108
                    wb.SHEETS(1).CELLS(rng, i).WrapText = True
                    wb.SHEETS(1).CELLS(rng, i).Orientation = 0
                    wb.SHEETS(1).CELLS(rng, i).AddIndent = False
                    wb.SHEETS(1).CELLS(rng, i).IndentLevel = 0
                    wb.SHEETS(1).CELLS(rng, i).ShrinkToFit = False
                    wb.SHEETS(1).CELLS(rng, i).MergeCells = False
                    wb.SHEETS(1).CELLS(rng, i).ReadingOrder = -5002
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("NombreRuta").ToString.Trim
            For j = 2 To 8
                wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

            Next
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("IdLiquidacion")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NoEconomico").ToString.Trim
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("NombreOperador").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("TotalBoletos")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("Liquidacion")
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Vueltas")
            If rdBuscar("TipoLiquidacion") = 0 Then
                wb.SHEETS(1).cells(rng, 8).value = "Por Boletos"
            Else
                wb.SHEETS(1).cells(rng, 8).value = "Por Cuota"
            End If

            totalUB = totalUB + wb.SHEETS(1).cells(rng, 5).value
            TotalL = TotalL + wb.SHEETS(1).cells(rng, 6).value
            totalV = totalV + wb.SHEETS(1).cells(rng, 7).value

            totalUBG = totalUBG + wb.SHEETS(1).cells(rng, 5).value
            TotalLG = TotalLG + wb.SHEETS(1).cells(rng, 6).value
            totalVG = totalVG + wb.SHEETS(1).cells(rng, 7).value
            rng = rng + 1
        Loop
        For j = 2 To 8
            wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

        Next
        wb.SHEETS(1).cells(rng, 4).value = "Totales"
        wb.SHEETS(1).cells(rng, 5).value = totalUB
        wb.SHEETS(1).cells(rng, 6).value = TotalL
        wb.SHEETS(1).cells(rng, 7).value = totalV
        For i = 4 To 7
            wb.SHEETS(1).cells(rng, i).font.italic = True
        Next

        rng = rng + 2
        For j = 2 To 8
            wb.SHEETS(1).cells(rng, j).FONT.name = "Segoe UI Light"

        Next
        wb.SHEETS(1).cells(rng, 2).value = "Total Autobuses: " & BuscarIntDatoCitra("SELECT COUNT(*) FROM Liquidacion WHERE convert(varchar,fecha,101)='" & strFecha1 & "'")
        wb.SHEETS(1).cells(rng, 2).font.bold = True
        wb.SHEETS(1).cells(rng, 4).value = "Total General"
        wb.SHEETS(1).cells(rng, 5).value = totalUBG
        wb.SHEETS(1).cells(rng, 6).value = TotalLG
        wb.SHEETS(1).cells(rng, 7).value = totalVG
        For i = 4 To 7
            wb.SHEETS(1).cells(rng, i).font.italic = True
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next

        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(1, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Liquidacion del " & Me.datFecha1.Value.ToString("d") & " (Filtrado por Ruta)"
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        wb.SHEETS(1).cells(3, 3).FONT.name = "Segoe UI Light"

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        Close()

    End Sub
End Class